package edu.hunau.cxb18.jdbc.dao.impl;

import edu.hunau.cxb18.jdbc.dao.UserDao;
import edu.hunau.cxb18.jdbc.pojo.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.time.LocalDateTime;
import java.util.List;

/**
 * @author hb
 * @create 2020-03-18 11:02
 */
@Repository
public class UserDaoImpl implements UserDao {
    @Autowired
    private JdbcTemplate jdbcTemplate;


    @Override
    public int inserUser(User user) {
        final String sql = "insert into t_user(username,pwd) values (?,md5(?))";

        KeyHolder keyHolder = new GeneratedKeyHolder();
        jdbcTemplate.update(
                (Connection connection) -> {
                    PreparedStatement ps = connection.prepareStatement(sql, new String[]{"id"});
                    ps.setString(1, user.getUsername());
                    ps.setString(2, user.getPwd());
                    return ps;
                }, keyHolder);
        return keyHolder.getKey().intValue();
    }

    @Override
    public void deleteById(Integer id) {

    }

    @Override
    public void updatePwdById(User user) {

    }

    @Override
    public String queryNameById(Integer id) {
        return jdbcTemplate.queryForObject("select username from t_user where id=?", String.class, id);
    }

    @Override
    public User queryUserById(Integer id) {
        String sql = "select id,username,pwd,dotime,isActive from t_user where id=? ";
        Object[] args = {id};

        return jdbcTemplate.queryForObject(sql, args, (rs,i) -> {
            User u = new User();
            u.setId(rs.getInt("id"));
            u.setUsername(rs.getString("username"));
            u.setPwd(rs.getString("pwd"));
            u.setActive(rs.getBoolean("isActive"));
            Timestamp timestamp = rs.getTimestamp("dotime");
            u.setDotime(timestamp.toLocalDateTime());
            return u;
        });
    }

    @Override
    public List<User> queryUsersByLikeName(String name) {
        String sql = "select id,username,pwd,dotime,isActive from t_user where username like concat('%',?,'%')";
        Object[] args = {name};
        return jdbcTemplate.query(sql, args, (rs,i) -> {
            User u = new User();
            u.setId(rs.getInt("id"));
            u.setUsername(rs.getString("username"));
            u.setPwd(rs.getString("pwd"));
            u.setActive(rs.getBoolean("isActive"));
            return u;
        });
    }

    @Override
    public List<User> queryAll() {
        return jdbcTemplate.query("select * from t_user",new BeanPropertyRowMapper<User>(User.class));
    }
}
